﻿using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;

namespace CC.Common
{
    public static class XlsHelper 
    {
        
        /// <summary>
        /// 构造函数私有化以禁止访问
        /// </summary>
        //private XlsHelper() { }

        /// <summary>
        /// 将DataTable的数据写入到excel中
        /// </summary>
        /// <param name="tables">要写入的Datatable</param>
        /// <param name="sheetnames">对应的sheet名，如果为null，则使用默认的"Sheet1"...</param>
        /// <param name="HDR">是否导入Datatable的列名</param>
        /// <returns>导入的数据行数（包含列名那行）</returns>
        public static int DatatabletoExcel(string filename, IEnumerable<DataTable> tables, IEnumerable<string> sheetnames, bool HDR = true, FileMode fileMode = FileMode.OpenOrCreate)
        {
            int count = 0;
            ISheet sheet = null;
            IWorkbook workbook = null;
            if (File.Exists(filename))
            {
                if (fileMode == FileMode.Append || fileMode == FileMode.OpenOrCreate)
                {
                    using (FileStream fs = File.Open(filename, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
                    {
                        //把xlsx文件读入workbook变量里，之后就可以关闭了  
                        if (filename.EndsWith(".xlsx")) workbook = new XSSFWorkbook(fs);//xlsx 2007 Format
                        else if (filename.EndsWith(".xls")) workbook = new HSSFWorkbook(fs);//xls, 2003 format
                    }
                }
                else
                {
                    File.Delete(filename);
                    if (filename.Contains(".xlsx")) workbook = new XSSFWorkbook();//xlsx 2007 Format
                    else if (filename.Contains(".xls")) workbook = new HSSFWorkbook();//xls, 2003 format
                }
            }
            else
            {
                if (filename.Contains(".xlsx")) workbook = new XSSFWorkbook();//xlsx 2007 Format
                else if (filename.Contains(".xls")) workbook = new HSSFWorkbook();//xls, 2003 format
            }

            if (workbook == null)
            {
                return -1;
            }
            for (int t = 0; t < tables.Count(); t++)
            {
                DataTable table = tables.ElementAt(t);
                if (sheetnames == null || t >= sheetnames.Count())
                {
                    sheet = workbook.CreateSheet();
                }
                else
                {
                    sheet = workbook.CreateSheet(sheetnames.ElementAt(t));
                }
                if (HDR)
                {
                    IRow row = sheet.CreateRow(0);
                    {
                        for (int j = 0; j < table.Columns.Count; j++)
                        {
                            row.CreateCell(j).SetCellValue(table.Columns[j].ColumnName);
                        }
                        count = 1;
                    }
                }
                else count = 0;
                for (int i = 0; i < table.Rows.Count; i++)
                {
                    IRow row = sheet.CreateRow(count);
                    for (int j = 0; j < table.Columns.Count; j++)
                    {
                        Type type = table.Rows[i][j].GetType();
                        if (type.Name == "Double" || type.Name == "Single" || type.Name == "Int32" || type.Name == "Int16" || type.Name == "int64")
                        {
                            row.CreateCell(j).SetCellValue(Convert.ToDouble(table.Rows[i][j]));
                        }
                        else if (type.Name == "Boolean")
                        {
                            row.CreateCell(j).SetCellValue(Convert.ToBoolean(table.Rows[i][j]));
                        }
                        else if (type.Name == "DateTime")
                        {
                            row.CreateCell(j).SetCellValue(Convert.ToDateTime(table.Rows[i][j]));
                        }
                        else
                        {
                            row.CreateCell(j).SetCellValue(table.Rows[i][j].ToString());
                        }
                    }
                    count++;
                }

            }
            FileStream sw = File.Create(filename);
            workbook.Write(sw);
            sw.Close();


            return count;
        }

       

        /// <summary>
        /// 将数据集合写入到excel中
        /// </summary>
        /// <param name="filename"></param>
        /// <param name="listObjects">要写入的数据，其属性名将写入标题，每个元素生成一行</param>
        /// <param name="sheetname">对应的sheet名，如果为null，则使用默认的"Sheet1"...</param>
        /// <param name="fileMode"></param>
        /// <param name="titleTranslation">标题字典，如果存在类属性名对应的字典，则读取字典value为列标题的值，否则读取属性名为列标题的值。Key为类属性名，Value为列标题名</param>
        /// <returns>导入的数据行数（包含列名那行）</returns>
        public static int ObjectListToExcel<T>(string filename, IEnumerable<T> listObjects, string sheetname,  FileMode fileMode, Dictionary<string,string>titleTranslation, params string[] excludeFields)
        {
            int count = 0;
            ISheet sheet = null;
            IWorkbook workbook = null;
            filename = filename.ToLower();
            if (File.Exists(filename))
            {

                if (fileMode == FileMode.Append || fileMode == FileMode.OpenOrCreate)
                {//尝试以追加方式打开文件，如果失败，则以覆盖方式打开
                    using (FileStream fs = File.Open(filename, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
                    {
                        //把xlsx文件读入workbook变量里，之后就可以关闭了  
                        if (filename.EndsWith(".xlsx")) workbook = new XSSFWorkbook(fs);//xlsx 2007 Format
                        else if (filename.EndsWith(".xls")) workbook = new HSSFWorkbook(fs);//xls, 2003 format
                    }
                }
                else
                {
                    File.Delete(filename);
                    if (filename.Contains(".xlsx")) workbook = new XSSFWorkbook();//xlsx 2007 Format
                    else if (filename.Contains(".xls")) workbook = new HSSFWorkbook();//xls, 2003 format
                }
            }
            else
            {
                if (filename.Contains(".xlsx")) workbook = new XSSFWorkbook();//xlsx 2007 Format
                else if (filename.Contains(".xls")) workbook = new HSSFWorkbook();//xls, 2003 format
            }

            if (workbook == null)
            {
                return -1;
            }
            if (string.IsNullOrEmpty( sheetname))
            {
                sheet = workbook.CreateSheet();
            }
            else
            {
                sheet = workbook.CreateSheet(sheetname);
            }
            //写入标题
            var props = listObjects.FirstOrDefault().GetType().GetProperties();
            IRow row = sheet.CreateRow(0);
            {
                for (int j = 0; j < props.Length; j++)
                {
                    if(excludeFields.Length>0)
                    {
                        if (excludeFields.Contains(props[j].Name)) continue;
                    }
                    if (titleTranslation!=null&&titleTranslation.ContainsKey(props[j].Name)) row.CreateCell(j).SetCellValue(titleTranslation[props[j].Name]);
                    else row.CreateCell(j).SetCellValue(props[j].Name);
                }
                count = 1;
            }
            //写入数据
            foreach (var obj in listObjects)
            {
                row = sheet.CreateRow(count++);
                
                for (int j = 0; j < props.Length; j++)
                {
                    if (excludeFields.Length > 0)
                    {
                        if (excludeFields.Contains(props[j].Name)) continue;
                    }
                    object value = props[j].GetValue(obj, null);
                    if(value!=null)
                    {
                        Type type = value.GetType();
                        if (type.Name == "Double" || type.Name == "Single" || type.Name == "Int32" || type.Name == "Int16" || type.Name == "int64")
                        {
                            row.CreateCell(j).SetCellValue(Convert.ToDouble(value));
                        }
                        else if (type.Name == "Boolean")
                        {
                            row.CreateCell(j).SetCellValue(Convert.ToBoolean(value));
                        }
                        else if (type.Name == "DateTime")
                        {
                            row.CreateCell(j).SetCellValue(value.ToString());
                        }
                        else
                        {
                            row.CreateCell(j).SetCellValue(value.ToString());
                        }
                    }
                   
                }
            }
            FileStream sw = File.Create(filename);
            workbook.Write(sw);
            sw.Close();
            return count;
        }
        public static List<string> GetSheetNames(string fileName)
        {
            var filename = fileName.ToLower();
            IWorkbook workbook;
            List<string> sheets = new List<string>();
            using (FileStream fs = new FileStream(filename, FileMode.Open, FileAccess.Read))
            {
                if (filename.Contains(".xlsx")) workbook = new XSSFWorkbook(fs);//xlsx 2007 Format
                else if (filename.Contains(".xlsm")) workbook = new XSSFWorkbook(fs);//xlsm, 2007 format
                else if (filename.Contains(".xls")) workbook = new HSSFWorkbook(fs);//xls, 2003 format
                else return sheets;
                for (int i = 0; i < workbook.NumberOfSheets; i++)
                {
                    sheets.Add(workbook.GetSheetAt(i).SheetName);
                }
                return sheets;
            }
        }
        /// <summary>
        /// 将excel文件的一个读出到一个类List
        /// 要求第一行内容为标题，可以用字典来指明类属性与excel标题的对应关系
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="filename">excel文件名</param>
        /// <param name="sheetIndex">页面索引</param>
        /// <param name="titleTranslation">类属性-列标题模板</param>
        /// <returns></returns>
        public static List<T> ExcelToOjectList<T>(string filename, int sheetIndex = 0, Dictionary<string, string> titleTranslation = null)
        {
            IWorkbook workbook = null;
            ISheet sheet = null;
            List<T> list = new List<T>();
            filename = filename.ToLower();
            using (FileStream fs = new FileStream(filename, FileMode.Open, FileAccess.Read))
            {
                if (filename.Contains(".xlsx")) workbook = new XSSFWorkbook(fs);//xlsx 2007 Format
                else if (filename.Contains(".xls")) workbook = new HSSFWorkbook(fs);//xls, 2003 format
                else return list;
                if (workbook.NumberOfSheets > sheetIndex)
                {
                    sheet = workbook.GetSheetAt(sheetIndex);
                }
                else return list;
                //1.查找每个属性对应的列号
                Dictionary<string, int> propVsColumns = new Dictionary<string, int>();
                var props = typeof(T).GetProperties();
                IRow row = sheet.GetRow(sheet.FirstRowNum);
                foreach (var prop in props)
                {

                    if (titleTranslation != null && titleTranslation.ContainsKey(prop.Name))
                    {
                        for (int i = row.FirstCellNum; i < row.LastCellNum; i++)
                        {
                            ICell cell = row.GetCell(i);
                            if (cell.CellType == CellType.String && cell.StringCellValue == titleTranslation[prop.Name])
                            {
                                propVsColumns.Add(prop.Name, i);
                                continue;
                            }
                        }
                    }
                    else
                    {
                        for (int i = row.FirstCellNum; i < row.LastCellNum; i++)
                        {
                            ICell cell = row.GetCell(i);
                            if (cell.CellType == CellType.String && cell.StringCellValue == prop.Name)
                            {
                                propVsColumns.Add(prop.Name, i);
                                continue;
                            }
                        }
                    }

                }

                //2.读入数据，存入List
                for (int i = sheet.FirstRowNum + 1; i <= sheet.LastRowNum; i++)
                {
                    row = sheet.GetRow(i);
                    if (row != null)
                    {
                        T obj = System.Activator.CreateInstance<T>();
                        foreach (var prop in props)
                        {
                            if (propVsColumns.ContainsKey(prop.Name))
                            {
                                ICell cell = row.GetCell(propVsColumns[prop.Name]);
                                if (cell != null)
                                {

                                    if (prop.PropertyType.IsEnum)
                                    {
                                        switch (cell.CellType)
                                        {
                                            case CellType.String:
                                                var val = (Enum.Parse(prop.PropertyType, cell.StringCellValue));
                                                prop.SetValue(obj, val, null);
                                                break;
                                            case CellType.Formula:
                                            case CellType.Numeric:
                                                prop.SetValue(obj, cell.NumericCellValue, null);
                                                break;
                                            default:
                                                break;
                                        }
                                    }
                                    else
                                    {
                                        switch (cell.CellType)
                                        {
                                            case CellType.String:
                                                prop.SetValue(obj, cell.StringCellValue, null);
                                                break;
                                            case CellType.Formula:
                                            case CellType.Numeric:
                                                prop.SetValue(obj, Convert.ChangeType(cell.NumericCellValue, prop.PropertyType), null);
                                                break;
                                            case CellType.Boolean:
                                                prop.SetValue(obj, cell.BooleanCellValue, null);
                                                break;
                                            default:
                                                break;
                                        }
                                    }
                                }
                            }
                        }
                        list.Add(obj);
                    }
                }
                return list;
            }

        }

        /// <summary>
        /// 往sheet的rowPosition中插入Datatable
        /// </summary>
        /// <param name="sheetname"></param>
        /// <param name="table"></param>
        /// <param name="rowIndex"></param>
        /// <returns></returns>
        public static int InsertRows(string file, string sheetname, DataTable table, int rowIndex)
        {
            file = file.ToLower();
            /* 从rowIndex到结尾的所有行移动datatable的行数，腾出空行；  插入table的所有行*/
            IWorkbook workbook = null;
            //1.装入已有文件到workbook,模式为读写
            using (FileStream fs = File.Open(file, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
            {
                //把xlsx文件读入workbook变量里，之后就可以关闭了  
                if (file.EndsWith(".xlsx")) workbook = new XSSFWorkbook(fs);//xlsx 2007 Format
                else if (file.EndsWith(".xls")) workbook = new HSSFWorkbook(fs);//xls, 2003 format
            }
            ISheet sheet = workbook.GetSheet(sheetname);
            if (sheet.LastRowNum <= 0) return -1;
            //2. 向下移动原有的内容，位移量为datatable的行数
            sheet.ShiftRows(rowIndex, sheet.LastRowNum - rowIndex, table.Rows.Count);
            //3. 插入datatable的内容           
            for (int i = 0; i < table.Rows.Count; i++)
            {
                IRow row = sheet.CreateRow(rowIndex + i);
                for (int j = 0; j < table.Columns.Count; j++)
                {
                    Type type = table.Rows[i][j].GetType();
                    if (type.Name == "Double" || type.Name == "Single" || type.Name == "Int32" || type.Name == "Int16" || type.Name == "int64")
                    {
                        row.CreateCell(j).SetCellValue(Convert.ToDouble(table.Rows[i][j]));
                    }
                    else if (type.Name == "Boolean")
                    {
                        row.CreateCell(j).SetCellValue(Convert.ToBoolean(table.Rows[i][j]));
                    }
                    else if (type.Name == "DateTime")
                    {
                        row.CreateCell(j).SetCellValue(Convert.ToDateTime(table.Rows[i][j]));
                    }
                    else
                    {
                        row.CreateCell(j).SetCellValue(table.Rows[i][j].ToString());
                    }


                }
            }
            FileStream sw = File.Create(file);
            workbook.Write(sw);
            sw.Close();
            return table.Rows.Count;
        }

        public static string[] GetColnames(ISheet sheet,int titleRowIndex=0)
        {
            List<string> colnames = new List<string>();
            if (sheet != null)
            {
                IRow firstRow = sheet.GetRow(titleRowIndex);
                int cellCount = firstRow.LastCellNum;//最后一个cell的编号，即为总列数
                int startCell = firstRow.FirstCellNum;
                for (int i = startCell; i < cellCount; i++)
                {
                    ICell cell = firstRow.GetCell(i);
                    if (cell != null)
                    {
                        string cellValue;
                        if (cell.CellType != CellType.String)
                            cellValue = cell.NumericCellValue.ToString();
                        else
                            cellValue = cell.StringCellValue;
                        colnames.Add(cellValue);
                    }
                }
            }
            return colnames.ToArray();
        }
        public static string[] GetColnames(string filename, int sheetIndex ,int titleRowIndex=0)
        {
            IWorkbook workbook = null;
            ISheet sheet = null;
            DataTable data = new DataTable();
            using (FileStream fs = new FileStream(filename, FileMode.Open, FileAccess.Read))
            {
                if (filename.Contains(".xlsx")) workbook = new XSSFWorkbook(fs);//xlsx 2007 Format
                else if (filename.Contains(".xlsm")) workbook = new XSSFWorkbook(fs);//xlsm, 2007 format
                else if (filename.Contains(".xls")) workbook = new HSSFWorkbook(fs);//xls, 2003 format
                else return new string[0];
                if (workbook.NumberOfSheets > sheetIndex)
                {
                    sheet = workbook.GetSheetAt(sheetIndex);
                    return GetColnames(sheet, titleRowIndex);
                }
                else
                {
                    return new string[0];
                }
            }
        }

        /// <summary>
        /// 将指定sheet的数据导入datatable
        /// </summary>
        /// <param name="sheetIndex">sheet编号，从0开始</param>
        /// <param name="HDR">是否有列标题</param>
        /// <param name="HDC">是否有行标题</param>
        /// <returns></returns>
        public static DataTable ExcelToDatatable(string filename, int sheetIndex = 0, bool HDR = true, bool HDC = false)
        {
            filename = filename.ToLower();
            IWorkbook workbook = null;
            ISheet sheet = null;
            DataTable data = new DataTable();
            using (FileStream fs = new FileStream(filename, FileMode.Open, FileAccess.Read))
            {
                if (filename.Contains(".xlsx")) workbook = new XSSFWorkbook(fs);//xlsx 2007 Format
                else if (filename.Contains(".xlsm")) workbook = new XSSFWorkbook(fs);//xlsm, 2007 format
                else if (filename.Contains(".xls")) workbook = new HSSFWorkbook(fs);//xls, 2003 format
                else return data;
                if (workbook.NumberOfSheets > sheetIndex)
                {
                    sheet = workbook.GetSheetAt(sheetIndex);
                    data = ReadData(sheet, HDR, HDC);
                }
            }

            return data;
        }
        /// <summary>
        /// 读取excel文件中的指定表，指定行为标题，指定数据开始行，指定字段的数据，读回的数据按指定的列标题存放
        /// </summary>
        /// <param name="sheetIndex">sheet编号，从0开始</param>
        /// <param name="titleRow">标题所在行</param>
        /// <param name="dataStartRow">数据开始行</param>
        /// <param name="selectAndChangetitles">传入的标题字典，选取字典的key为datatable的列标题，并读取excel中列标题为对应value的值</param>
        /// <returns></returns>
        public static DataTable ExcelToDatatable(string filename, int sheetIndex,int titleRow,int dataStartRow,Dictionary<string,string> selectAndChangetitles)
        {
            filename = filename.ToLower();
            IWorkbook workbook = null;
            //1.初始化构建datatable列
            DataTable table = new DataTable();
            foreach (var item in selectAndChangetitles)
            {
                table.Columns.Add(item.Key);
            }
            //打开文件
            using (FileStream fs = new FileStream(filename, FileMode.Open, FileAccess.Read))
            {
                if (filename.Contains(".xlsx")) workbook = new XSSFWorkbook(fs);//xlsx 2007 Format
                else if (filename.Contains(".xlsm")) workbook = new XSSFWorkbook(fs);//xlsm, 2007 format
                else if (filename.Contains(".xls")) workbook = new HSSFWorkbook(fs);//xls, 2003 format
                else return table;
                
                Dictionary<string, int> colPositions = new Dictionary<string, int>();
                //存放每列数据对应的excel数据列
                if (workbook.NumberOfSheets > sheetIndex)
                {//容错处理
                    ISheet sheet = workbook.GetSheetAt(sheetIndex);
                    if (sheet != null)
                    {
                        //2.分析标题行，找到标题对应的列索引
                        var colTitles = GetColnames(sheet, titleRow).ToList();//读出所有的列标题
                        foreach (var item in selectAndChangetitles)
                        {
                            int index = colTitles.IndexOf(item.Value);
                            colPositions.Add(item.Key, index);
                        }
                        //3.逐行扫描，读取每列对应的单元格，生成datarow
                        for (int i = dataStartRow; i <= sheet.LastRowNum; i++)
                        {
                            DataRow row = table.NewRow();
                            for (int j = 0; j < table.Columns.Count; j++)
                            {
                                row[j] = ReadCellString(sheet, i, colPositions[table.Columns[j].ColumnName]);
                            }
                            //4.datatable加入行
                            table.Rows.Add(row);
                        }
                    }

                }
               
            }
            

            return table;
        }

        private static object ReadCellValue(ISheet sheet, int rowIndex, int columnIndex)
        {
            if (rowIndex < 0 || columnIndex < 0) return null;
            IRow row = sheet.GetRow(rowIndex);
            ICell cell = row.GetCell(columnIndex);            
            if (cell != null)
            {
                switch (cell.CellType)
                {
                    case CellType.Unknown:
                        return "UnKnown";
                    case CellType.Numeric:
                        return cell.NumericCellValue;                        
                    case CellType.String:
                        return cell.StringCellValue;
                    case CellType.Formula:
                        return cell.DateCellValue;                        
                    case CellType.Blank:
                        return null;
                    case CellType.Boolean:
                        return cell.BooleanCellValue;                        
                    case CellType.Error:
                        return cell.ErrorCellValue;                        
                    default:
                        return null;                        
                }    
            }
            return null;
        }

        private static string ReadCellString(ISheet sheet, int rowIndex, int columnIndex)
        {
            object val = ReadCellValue(sheet, rowIndex, columnIndex);
            if(val!=null)
            {
                return val.ToString();
            }
            return null;
        }
        /// <summary>
        /// EXCEL数据导入DataTable
        /// </summary>
        /// <param name="sheetname">工作表名称</param>
        /// <param name="HDR">是否导入列标题</param>
        /// <param name="HDC">是否有行标题</param>
        /// <returns></returns>
        public static DataTable ExcelToDatatable(string filename, string sheetname, bool HDR = true, bool HDC = false)
        {
            filename = filename.ToLower();
            IWorkbook workbook = null;
            ISheet sheet = null;
            DataTable data = new DataTable();

            using (FileStream fs = new FileStream(filename, FileMode.Open, FileAccess.Read))
            {
                if (filename.Contains(".xlsx")) workbook = new XSSFWorkbook(fs);//xlsx 2007 Format
                else if (filename.Contains(".xlsm")) workbook = new XSSFWorkbook(fs);//xlsm, 2007 format
                else if (filename.Contains(".xls")) workbook = new HSSFWorkbook(fs);//xls, 2003 format
                if (!string.IsNullOrEmpty(sheetname))
                {
                    sheet = workbook.GetSheet(sheetname);
                    if (sheet == null)
                    {//如果没找到指定的sheet，则尝试获取第一个sheet
                        sheet = workbook.GetSheetAt(0);
                    }
                }
                else
                {
                    sheet = workbook.GetSheetAt(0);
                }
                if (sheet != null)
                {
                    data = ReadData(sheet, HDR, HDC);
                }
            }

            return data;

        }
        private static DataTable ReadData(ISheet sheet, bool HDR, bool HDC)
        {
            DataTable data = new DataTable();
            int startRow = 0;
            if (sheet != null&&sheet.PhysicalNumberOfRows>0)
            {
                IRow firstRow = sheet.GetRow(0);
                int cellCount = firstRow.LastCellNum;//最后一个cell的编号，即为总列数
                int startCell = firstRow.FirstCellNum;
                if (HDC) startCell++;
                if (HDR)
                {
                    for (int i = startCell; i < cellCount; i++)
                    {
                        ICell cell = firstRow.GetCell(i);
                        if (cell != null)
                        {
                            string cellValue;
                            if (cell.CellType != CellType.String)
                                cellValue = cell.NumericCellValue.ToString();
                            else
                                cellValue = cell.StringCellValue;
                            if (!string.IsNullOrEmpty(cellValue))
                            {
                                DataColumn column = new DataColumn(cellValue);
                                data.Columns.Add(column);
                            }
                        }
                    }
                    cellCount = data.Columns.Count;//单元数量修正为标题栏数量
                    startRow = sheet.FirstRowNum + 1;
                }
                else
                {
                    startRow = sheet.FirstRowNum;
                    for (int i = startCell; i < cellCount; i++)
                    {
                        data.Columns.Add("Col" + i.ToString());
                    }
                }
                int rowCount = sheet.LastRowNum;

                for (int i = startRow; i <= rowCount; i++)
                {//遍历数据行
                    //StringBuilder sb = new StringBuilder();
                    //sb.Append("row[" + i + "] ");
                    IRow row = sheet.GetRow(i);
                    if (row == null)
                    {
                        //System.Diagnostics.Debug.WriteLine(sb.ToString());
                        continue;//没有数据的行默认是null
                    }
                        
                    System.Data.DataRow dataRow = data.NewRow();
                    while (cellCount < row.LastCellNum)
                    {
                        data.Columns.Add(new DataColumn());
                        cellCount = data.Columns.Count;
                    }
                    //if (row.LastCellNum >= cellCount)
                    {//要求数据行单元格比标题数多，以免读出来的数据无法转换
                        bool empty = true;
                        for (int j = startCell; j < cellCount; j++)
                        {
                            //sb.Append("(" + j + "){");
                            ICell cell = row.GetCell(j);
                            //if (cell == null)
                            //    sb.Append("nullcell");
                            //else
                            //    sb.Append(cell.ToString());
                            //sb.Append("}");

                            // if (cell.CellType != CellType.Blank)//没有数据的单元默认是blank
                            if (cell != null)
                            {
                                empty = false;
                                if (cell.CellType == CellType.String)
                                    dataRow[j - startCell] = cell.StringCellValue;//.ToString();

                                else
                                    dataRow[j - startCell] = cell.NumericCellValue;
                            }

                        }
                        if (!empty)
                            data.Rows.Add(dataRow);
                    }
                    //System.Diagnostics.Debug.WriteLine(sb.ToString());

                }
            }
            return data;
        }
      
       
        public static ISheet GetSheet(string filename, int sheetIndex = 0)
        {
            filename = filename.ToLower();
            IWorkbook workbook = null;
            ISheet sheet = null;

            using (FileStream fs = new FileStream(filename, FileMode.Open, FileAccess.Read))
            {
                if (filename.Contains(".xlsx")) workbook = new XSSFWorkbook(fs);//xlsx 2007 Format
                else if (filename.Contains(".xls")) workbook = new HSSFWorkbook(fs);//xls, 2003 format
                else return sheet;
                if (workbook.NumberOfSheets > sheetIndex)
                {
                    sheet = workbook.GetSheetAt(sheetIndex);
                }
                return sheet;
            }
        }
      
      

    }
}
